package org.eclipse.epsilon.emc.rdbms.core.persistancebroker;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;


import org.eclipse.epsilon.emc.rdbms.core.classmap.ClassMap;
import org.eclipse.epsilon.emc.rdbms.core.customexceptions.CustomException;
import org.eclipse.epsilon.emc.rdbms.core.imodel.DBReflexiveModelElement;
import org.eclipse.epsilon.emc.rdbms.core.imodeltransactionsupport.imodelTransactionSupport;
import org.eclipse.epsilon.emc.rdbms.core.persistancemechanism.H2Database;
import org.eclipse.epsilon.emc.rdbms.core.persistancemechanism.RelationalDatabase;
import org.eclipse.epsilon.emc.rdbms.core.persistancemechanism.SQLDatabase;
import org.eclipse.epsilon.emc.rdbms.core.persistanttransaction.PersistanceTransaction;

public class PersistanceBroker 
{
	private static PersistanceBroker instance = null;
	public PersistanceBroker() {
		// Exists only to defeat instantiation.
	}

	/**
	 * This is used to instantiate the persistance Broker as this is a singleton class this first checks to see if another persistance broker object already exists.
	*/
	public static PersistanceBroker getInstance() {
		if(instance == null) {
			instance = new PersistanceBroker();
		}
		return instance;
	}

	/**
	 * This processes calls for the sql statament to be generated and then adds the statement to the Transaction class
	* @param element This is the element that is to be added to the database
	* @param property This is the properties to be added, these will override the default values specified in the TypeMapper class
	* @return result. This returns the result set from the transaction.
	*/		
	public DBReflexiveModelElement saveObject(DBReflexiveModelElement element,HashMap<String, Object> properties) throws SQLException
	{
		ClassMap objectMap = new ClassMap(); 	
		PersistanceTransaction.saveObject(objectMap.getInsertSQLFor(element,properties)); 		
		return  null;
	}


	/**
	* This method retrieves the element information from the database
	* @param element This is the element that is to be added to the database
	* @param property This is the properties to be added, these will override the default values specified in the TypeMapper class
	* @return result. This returns the result set from the transaction.
	*/		
	public static ResultSet retrieveObject(DBReflexiveModelElement element, ArrayList<String> properties) throws SQLException
	{ 
		ResultSet result = null;
		ArrayList<String> columns = new ArrayList();
		if(properties != null &&  properties.isEmpty()==false && properties.size()==1)
		{
			//Here we know that there is something in the properties specified.
			if(properties.get(0).equals("all")==true)  
			{
				RelationalDatabase connection = connectTo();
				columns = ResultSetToArrayList(connection.getSQLColumns(element.getType()), "COLUMN_NAME");
				ClassMap objectMap = new ClassMap();
				String statement = objectMap.getSelectSQLFor(element,columns);
				System.out.println(statement);
				Statement stmt = connection.open().createStatement();
				return result = stmt.executeQuery(statement);
			}
			else if(properties.get(0).equals("all")==false) 
			{
				//So now we need to check if the column specified exists in the table
				boolean propexists = false; //boolean to flag if the properties exists or not.
				RelationalDatabase connection = connectTo();
				ResultSet rs = connection.getSQLColumns(element.getType());
				while(rs.next())
				{
					if(properties.get(0).toUpperCase().equals(rs.getString("COLUMN_NAME").toUpperCase()))
					{
						columns = properties;
						propexists = true;
						ClassMap objectMap = new ClassMap();
						String statement = objectMap.getSelectSQLFor(element,columns);
						System.out.println(statement);
						Statement stmt = connection.open().createStatement();
						return result = stmt.executeQuery(statement);
					}
				}
				if(propexists==false)
				{
					//So here we know that either we have a foreign key specified or that 1 column has been specified which is incorrect.
					//Check the foreign Key relationships.
					ResultSet exportedKeys = connection.getExportedKeys(element);
					boolean FKNameFound=false;
					while(exportedKeys.next())
					{	
						if(exportedKeys.getString("FK_NAME").toUpperCase().equals(properties.get(0).toUpperCase()))
						{			
						FKNameFound=true;
					        //We use ArrayLists here as the Relation query has been designed to do multiple nested inner joins if neccessary.
					        ArrayList<String> PKcols = new ArrayList();
					        ArrayList<String> PKtables = new ArrayList();
					        ArrayList<String> FKcols = new ArrayList(); 
					        ArrayList<String> FKtables = new ArrayList();
							PKcols.add("fktable."+exportedKeys.getString("FKCOLUMN_NAME"));  //This should be the PK column from the FK table. 
							PKtables.add(exportedKeys.getString("FKTABLE_CAT")+"."+exportedKeys.getString("FKTABLE_SCHEM")+"."+ exportedKeys.getString("FKTABLE_NAME")); 
							FKcols.add("pktable."+exportedKeys.getString("PKCOLUMN_NAME"));
							FKtables.add(exportedKeys.getString("PKTABLE_CAT")+"."+exportedKeys.getString("PKTABLE_SCHEM")+"."+ exportedKeys.getString("PKTABLE_NAME"));
							ClassMap objectMap = new ClassMap();
							String statement = objectMap.getSelectWithJoinsFor(element,exportedKeys.getString("FKTABLE_NAME"),PKcols,PKtables,FKcols,FKtables); 
						
							System.out.println(statement);
							Statement stmt = connection.open().createStatement();
							result = stmt.executeQuery(statement); 	
							return result;
						}
					}
					if(FKNameFound==false)
					{
					//Now we need to check to see if this belongs to an imported Key column rather than exported.
					ResultSet importedKeys = connection.getImportedKeys(element.getType());
					while(importedKeys.next())
					{	
						if(importedKeys.getString("FK_NAME").toUpperCase().equals(properties.get(0).toUpperCase()))
						{	
						FKNameFound=true;						
					   //We use ArrayLists here as the Relation query has been designed to do multiple nested inner joins if neccessary.
					        ArrayList<String> PKcols = new ArrayList();
					        ArrayList<String> PKtables = new ArrayList();
					        ArrayList<String> FKcols = new ArrayList(); 
					        ArrayList<String> FKtables = new ArrayList();
				   
							PKcols.add("fktable."+importedKeys.getString("PKCOLUMN_NAME"));
							PKtables.add(importedKeys.getString("PKTABLE_CAT")+"."+importedKeys.getString("PKTABLE_SCHEM")+"."+ importedKeys.getString("PKTABLE_NAME")); 
							FKcols.add("pktable."+importedKeys.getString("FKCOLUMN_NAME"));
							FKtables.add(importedKeys.getString("FKTABLE_CAT")+"."+importedKeys.getString("FKTABLE_SCHEM")+"."+ importedKeys.getString("FKTABLE_NAME"));
							ClassMap objectMap = new ClassMap();		
							String statement = objectMap.getSelectWithJoinsFor(element,importedKeys.getString("PKTABLE_NAME"),PKcols,PKtables,FKcols,FKtables); 					
							System.out.println(statement);
							Statement stmt = connection.open().createStatement();
							result = stmt.executeQuery(statement); 	
							return result;
					       }
					   }
					}
				}
				else 
				{
					//We know that the properties may be empty or have more than 1 column specified so we just need to add the columns to the array.	
					columns = properties;
					ClassMap objectMap = new ClassMap();
					String statement = objectMap.getSelectSQLFor(element,columns);
					System.out.println(statement);
					Statement stmt = connection.open().createStatement();
					return result = stmt.executeQuery(statement);
				}
			}		    
		}
		return result;
	}

	/**
	* This method retrieves the all the elements for a particular type.
	* @param type This is the type that is to be retried to the database
	* @param property This is the properties to be retrieved the columns specified in the select clause,
	* @param criteria This is the properties that will be in the where clause of the SQL statement.
	* @return result. This returns the result set from the transaction.
	*/		
	public static ResultSet retrieveFromType(String type, ArrayList<String> properties, HashMap<String, Object> criteria) throws SQLException 
	{
		ResultSet result = null;
		ArrayList<String> columns = new ArrayList();
		RelationalDatabase connection = connectTo();
		
		if(properties != null &&  properties.isEmpty()==false && properties.size()==1)
		{
			//Here we know that there has been all has been specified.
			if(properties.get(0).equals("all")==true)  
			{
			columns = ResultSetToArrayList(connection.getSQLColumns(type), "COLUMN_NAME");
			ClassMap objectMap = new ClassMap();
			String statement = objectMap.getSelectSQLForType(type,columns, criteria);
			Statement stmt = connection.open().createStatement();
			return result = stmt.executeQuery(statement);
			}
			else if(properties.get(0).equals("all")==false) 
			{
				//So now we need to check if the column specified exists in the table
				boolean propexists = false; //boolean to flag if the properties exists or not.
				ResultSet rs = connection.getSQLColumns(type);
				while(rs.next())
				{
					if(properties.get(0).equals(rs.getString("COLUMN_NAME")))
					{
					columns = properties;
					propexists = true;
					ClassMap objectMap = new ClassMap();
					String statement = objectMap.getSelectSQLForType(type,columns, criteria);
					Statement stmt = connection.open().createStatement();
					return result = stmt.executeQuery(statement);
					}
				}
				if(propexists==false)
				{
					//So here we know that either we have a foreign key specified or that 1 column has been specified which is incorrect.
					ResultSet importedKeys = connection.getImportedKeys(type);
					while(importedKeys.next())
					{
					if(importedKeys.getString("FK_NAME").equals(properties.get(0)))
					{
					//We use ArrayLists here as the Relation query has been designed to do multiple nested inner joins if neccessary.
					ArrayList<String> PKcols = new ArrayList();
					ArrayList<String> PKtables = new ArrayList();
					ArrayList<String> FKcols = new ArrayList(); 
					ArrayList<String> FKtables = new ArrayList();

					PKcols.add("fktable."+importedKeys.getString("PKCOLUMN_NAME"));
					//PKcols.add(importedKeys.getString("PKTABLE_CAT")+"."+importedKeys.getString("PKTABLE_SCHEM")+"."+ importedKeys.getString("PKTABLE_NAME")+"."+importedKeys.getString("PKCOLUMN_NAME")); 
					PKtables.add(importedKeys.getString("PKTABLE_CAT")+"."+importedKeys.getString("PKTABLE_SCHEM")+"."+ importedKeys.getString("PKTABLE_NAME")); 
					FKcols.add("fktable."+importedKeys.getString("FKCOLUMN_NAME"));
					//FKcols.add(importedKeys.getString("FKTABLE_CAT")+"."+importedKeys.getString("FKTABLE_SCHEM")+"."+ importedKeys.getString("FKTABLE_NAME")+"."+importedKeys.getString("FKCOLUMN_NAME"));     			  
					FKtables.add(importedKeys.getString("FKTABLE_CAT")+"."+importedKeys.getString("FKTABLE_SCHEM")+"."+ importedKeys.getString("FKTABLE_NAME"));
					ClassMap objectMap = new ClassMap();
					String statement = objectMap.getSelectWithJoinsForType(importedKeys.getString("PKTABLE_NAME"),importedKeys.getString("FKTABLE_NAME"),PKcols,PKtables,FKcols,FKtables); 			   
					Statement stmt = connection.open().createStatement();
					result = stmt.executeQuery(statement); 	
					return result;
					}
					}  		
				}
				else 
				{
				//We know that the properties may be empty or have more than 1 column specified so we just need to add the columns to the array.	
				columns = properties;
				ClassMap objectMap = new ClassMap();
				String statement = objectMap.getSelectSQLForType(type,columns, criteria);
				Statement stmt = connection.open().createStatement();
				return result = stmt.executeQuery(statement);
				}
			}		    

		}
		else if(properties == null)
		{
			
			columns = ResultSetToArrayList(connection.getSQLColumns(type), "COLUMN_NAME");
			ClassMap objectMap = new ClassMap();
			String statement = objectMap.getSelectSQLForType(type,columns, criteria);
			Statement stmt = connection.open().createStatement();
			return result = stmt.executeQuery(statement);
		}
		else
		{
			new CustomException("ERROR: PersistanceBroker - Unable to retireve data from type "+type);
		}
		return result;
	}

	/**
	* This method deletes an element from the database
	* @param element This is the element that is to be deleted from the database
	*/		
	public static  void deleteObject(DBReflexiveModelElement element) throws SQLException
	{
		ClassMap objectMap = new ClassMap(); 	
		PersistanceTransaction.saveObject(objectMap.getDeleteSQLFor(element)); 		
	}

	/**
	* This method deletes the all the elements for a particular type.
	* @param type This is the type that is to be deleted from the database
	* 	
	* */		

	public static void deleteType(String type) throws SQLException
	{
		ClassMap objectMap = new ClassMap(); 	
		PersistanceTransaction.saveObject(objectMap.getDeleteSQLForType(type)); 		
	}


	/**
	* This method updates an element that is in the database.
	* @param element This is the element that is to be deleted from the database
	* @param properties This is the Hashmap of the column value information and will contain the data and attribute to be updated
	*/		
	public void updateObject(DBReflexiveModelElement element,HashMap properties) throws SQLException 
	{
		System.out.println("You are here");
		ClassMap objectMap = new ClassMap(); 
		//So here we have the hashmap which may contain a model element or Object of Type so we need to test and update the HashMap accordingly.
		//We only want to do this if the column that is to be updated is not part of the primary Key field.
		PersistanceTransaction.saveObject(objectMap.getUpdateSQLFor(element,properties)); 
		
	}

	/**
	* This method updates all elements of a particular type in the database.
	* @param tablename This is the type that is to be deleted from the database
	* @param properties This is the Hashmap of the column value information that will be specified in the from clause
	*/		
	public void updateType(String tablename,HashMap properties) throws SQLException
	{
		ClassMap objectMap = new ClassMap(); 	
		PersistanceTransaction.saveObject(objectMap.getUpdateSQLForType(tablename,properties));
	}


	/**
	* This method retrieves the data from the database based on the foreign key relationships between 2 tables.
	* @param element This is the element that is to be the primary table in the join
	* @param fk_name This is the name of the foreign key specified in the database schema and will be used to associate which tables and columns need to be joined together.
	*/			
	public static ResultSet retrieveFromTypeWithJoins(DBReflexiveModelElement element,String fk_name) throws SQLException
	{
		//So here we have the primary table and the foreign key name that has been passed to this method.
		//so now we need to get all the info from the table and look for the relevant foreign key information.
		ResultSet importedKeys = connectTo().getImportedKeys(element.getType());
		while(importedKeys.next())
		{
			if(importedKeys.getString("FK_NAME").equals(fk_name))
			{	 
				ClassMap objectMap = new ClassMap();

				//We use ArrayLists here as the Relation query has been designed to do multiple nested inner joins if neccessary.
				ArrayList<String> PKcols = new ArrayList();
				ArrayList<String> PKtables = new ArrayList();
				ArrayList<String> FKcols = new ArrayList(); 
				ArrayList<?> FKtables = new ArrayList();

				PKcols.add("pkcols"+"."+importedKeys.getString("PKCOLUMN_NAME")); 
				PKtables.add(importedKeys.getString("PKTABLE_CAT")+"."+importedKeys.getString("PKTABLE_SCHEM")+"."+ importedKeys.getString("PKTABLE_NAME")); 
				FKcols.add("FKCols"+"."+importedKeys.getString("FKCOLUMN_NAME")); 
				PKtables.add(importedKeys.getString("FKTABLE_CAT")+"."+importedKeys.getString("FKTABLE_SCHEM")+"."+ importedKeys.getString("FKTABLE_NAME"));
				String statement = objectMap.getSelectWithJoinsFor(element,importedKeys.getString("FKTABLE_NAME"),PKcols,PKtables,FKcols,FKtables); 
			
				//RelationalDatabase connection = connectTo();
				//Statement stmt = connection.open().createStatement();
				//ResultSet test = stmt.executeQuery(statement); 	
				return null;
			}
			else
			{
				new CustomException("ERROR: PersistanceBroker - "+fk_name+" does not exists for type "+element.getType());
				return null;
			}
		}
		return null;
	}

	/**
	* This method connects to the database server and checks to see if the specified type has a related table with a primary Key. This means that this object can be instantiated into the database.
	* @param type The name of the table to check
	* @return Returns the resultset of the primaryKeys for the table
	*/			
	public ResultSet retrievehasType(String type) 
	{

		ResultSet hasType = connectTo().getPrimaryKey(type); 
		return hasType;
	}

	/**
	* This method connects to the database server and retrieves a list of all tables from the database.
	* @return types A list of all the tables in the database.
	*/			
	public ResultSet retrieveTypes() 
	{
		
		ResultSet types = connectTo().getSQLTables(null); 
		return types;
	}

	/**
	* This method connects to the database server and retrieves a list of all columns associated with a given table.
	* @param type The table that you want to get the list of columns for.
	* @return columns 
	*/			
	public static ResultSet retrieveColumns(String type)
	{
		ResultSet columns = connectTo().getSQLColumns(type);	
		return columns;
	}

	/**
	* This method connects to the database server and executes the specified queries against the database.
	* @param statement The TSQL statement to be executed.
	* @return The result of the statement. 
	*/			
	public static ResultSet processSql(String statement) throws SQLException
	{
		System.out.println(statement);
		Statement stmt =  PersistanceTransaction.getConnection().createStatement();  
		stmt.executeUpdate(statement,Statement.RETURN_GENERATED_KEYS); 	
		if(statement.substring(0, 6).equals("INSERT"))
		{
			ResultSet primaryKeyValues = stmt.getGeneratedKeys();
			return primaryKeyValues; 	
		}
		return null;
	}

	/**
	* This method connects to the database server
	* @return Connection  The connection to the database server. 
	*/			
	public static RelationalDatabase connectTo()
	{
		//RelationalDatabase Connection = new H2Database("jdbc:h2:\\localhost\\","~\\runtime-EclipseApplication\\org.eclipse.epsilon.emc.rdbms\\sample_db\\h2","epsilon"); //Create a new connection object.  
		RelationalDatabase Connection = new H2Database("jdbc:h2:","..//org.eclipse.epsilon.emc.rdbms//sample_db//h2","epsilon"); //Create a new connection object.  
		return Connection;
	}

	/**
	* This method disconnects and closes the connectiob to the database server
	* @param connection   The connection to be closed.
	*/			
	public static void disconnectFrom(Connection connection) throws SQLException
	{
		connection.close();

	}

	/**
	* This method is not implemented
	*/			
	private static void retrieveClassMaps()
	{
    //This is for future expansion as depending upon the performance testing we may need to put all the table data into classes and pull the class information rather than querying the database directly.
	}

	/**
	* This method adds the primary Key properties to the DBReflexiveModelElement.
	* @param element This is the newly created element that needs its properties assigning
	* @param primaryKeyNames This is the column names for the primary Key.
	* @param primaryKeyValues This is the values for the specific Primary Key for the element
	* @return element  The connection to the database server. 
	*/			
	public static DBReflexiveModelElement createElement(DBReflexiveModelElement element, ResultSet primaryKeyNames,ResultSet primaryKeyValues) throws SQLException
	{
		if (primaryKeyNames.next()) 
		{
			if (primaryKeyValues.next()) 
			{	
				
				element.put(primaryKeyNames.getString(4), primaryKeyValues.getString(1));
			}	 
		}
		return element; 
	}

	/**
	* This method turns a generic resultset into a generic ArrayList
	* @resultset The resultset to be turned into an arrayList
	* @columnName the columnName of the arrayList to be added to the resultset.
	* @return arrayList   
	*/			
	private static ArrayList<String> ResultSetToArrayList (ResultSet resultset, String columnName) throws SQLException
	{
		ArrayList<String>  arraylist = new ArrayList<String>(); 
		while (resultset.next()) 
		{		
			arraylist.add(resultset.getString(columnName)); //Add all the columns to the select list.
		}	
		return arraylist;
	}
}
